package com.cennavi.mineplay.mineplay_data.controller;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import no.ecc.vectortile.VectorTileEncoder;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.cennavi.mineplay.mineplay_data.model.transform.inter.impl.AddPointTransformImpl;
import com.mercator.TileUtils;
import com.vividsolutions.jts.geom.Geometry;
import com.vividsolutions.jts.io.ParseException;
import com.vividsolutions.jts.io.WKTReader;

@RestController
@RequestMapping(value = "/custom")
public class Customer {

	@Autowired
	protected JdbcTemplate jdbc;
	
	@Value("${service.pbf.prefix}")
	private String servicePbfPrefix;
	
	@Value("${service.geojson.prefix}")
	private String serviceGeojsonPrefix;
	
	
	@Value("${service.json.prefix}")
	private String serviceJsonPrefix;
	
	@Autowired
	protected  AddPointTransformImpl transform;
	
	
	private static String convertSql(String sql){
		String today = new SimpleDateFormat("yyyyMMdd").format(new Date());
		sql = sql.replace(" gps ", " gps_"+today+" ");
		return sql;
	}
	
	private static boolean judgeDate(String v){
		boolean flag = true;
		if (v.length() == 14){
			for(int j=0;j<14;j++){
				if (v.charAt(j) <='0' || v.charAt(j)>='9'){
					return false;
				}
			}
		}else{
			return false;
		}
		return flag;
	}

	@RequestMapping(value = "/pbf/{z}/{x}/{y}", produces = "application/x-protobuf")
	public byte[] pbf(@PathVariable int x, @PathVariable int y,
			@PathVariable int z, String param, int id) {

		String querySql = "select sql from data_service where id=" + id;

		String sql = jdbc.queryForList(querySql).get(0).get("sql").toString();

		try {

			String tile = TileUtils.parseXyz2Bound(x, y, z);
			JSONArray arrayParam = JSONArray.parseArray(param);
			if (param != null && arrayParam.size()>0) {
				// JSONObject jsonParam = JSONObject.parseObject(param);
				

				int idx = 0;

				while (sql.indexOf("?") > 0) {

					Object valObj = arrayParam.get(idx++);

					if (valObj instanceof String) {

						//sql = sql.replace("?", "'" + valObj.toString() + "'");
						
						sql = sql.substring(0, sql.indexOf("?")) +"'" + valObj.toString() + "'" + sql.substring(sql.indexOf("?")+1); 

						
					} else {
//						sql = sql.replace("?", valObj.toString());
						
						sql = sql.substring(0, sql.indexOf("?")) + valObj.toString()  + sql.substring(sql.indexOf("?")+1); 
					}
				}
				
				

				sql += " and st_intersects(geom,st_geomfromtext(?,4326)) ";

			} else {

				sql += " where st_intersects(geom,st_geomfromtext(?,4326)) ";

			}
			
			if (sql.indexOf(" gps ")>0){
				if (sql.indexOf("time<=?")>0 || sql.indexOf("time>=?")>0 || sql.indexOf("time between ?")>0){
					
					String datePrefix = null;
					
					for(int i=0;i<arrayParam.size();i++){
						String v = arrayParam.getString(i);
						
						if (Customer.judgeDate(v)){
							datePrefix = v.substring(0, 8);
							break;
						}
					}
					
					sql = sql.replace(" gps ", " gps_"+datePrefix+" ");
					
				}else{
					sql = Customer.convertSql(sql);
				}
			}

			List<Map<String, Object>> results = jdbc.queryForList(sql, tile);

			VectorTileEncoder vte = new VectorTileEncoder(4096, 16, false);

			for (Map<String, Object> m : results) {
				String wkt = (String) m.get("geom");

				Geometry geom = new WKTReader().read(wkt);

				TileUtils.convert2Piexl(x, y, z, geom);

				m.remove("geom");

				vte.addFeature("gps", m, geom);

			}

			return vte.encode();

		} catch (ParseException e) {
			e.printStackTrace();
		}

		return null;

	}

	@RequestMapping(value = "/geojson")
	public Object geojson(String param, int id) {

		String querySql = "select sql from data_service where id=" + id;

		String sql = jdbc.queryForList(querySql).get(0).get("sql").toString();
		
		boolean is_budian = false;
		
		int level = 0;
		
		if (sql.indexOf(";")>0){
			String temp = sql.split(";")[0];
			is_budian = true;
			level = Integer.parseInt(sql.split(";")[1]);
			sql = temp;
		}
		JSONArray arrayParam = JSONArray.parseArray(param);
		if (param != null && !"[]".equals(param)) {
			// JSONObject jsonParam = JSONObject.parseObject(param);
			

			int idx = 0;

			while (sql.indexOf("?") > 0) {

				Object valObj = arrayParam.get(idx++);

				if (valObj instanceof String) {

					//sql = sql.replace("?", "'" + valObj.toString() + "'");
					
					sql = sql.substring(0, sql.indexOf("?")) +"'" + valObj.toString() + "'" + sql.substring(sql.indexOf("?")+1); 

					
				} else {
//					sql = sql.replace("?", valObj.toString());
					
					sql = sql.substring(0, sql.indexOf("?")) + valObj.toString()  + sql.substring(sql.indexOf("?")+1); 
				}
			}

		}
		
		
		if (sql.indexOf(" gps ")>0){
			if (sql.indexOf("time<=?")>0 || sql.indexOf("time>=?")>0 || sql.indexOf("time between ?")>0){
				
				String datePrefix = null;
				
				for(int i=0;i<arrayParam.size();i++){
					String v = arrayParam.getString(i);
					
					if (Customer.judgeDate(v)){
						datePrefix = v.substring(0, 8);
						break;
					}
				}
				
				sql = sql.replace(" gps ", " gps_"+datePrefix+" ");
				
			}else{
				sql = Customer.convertSql(sql);
			}
		}

		List<Map<String, Object>> results = jdbc.queryForList(sql);

//		Map<String, Object> geojson = new HashMap<String, Object>();
		
		JSONObject geojson = new JSONObject();
		
		geojson.put("type", "FeatureCollection");
		
		List<Map<String,Object>> features = new ArrayList<Map<String,Object>>();
		
		for (Map<String, Object> m : results) {
			
			Map<String,Object> feature = new HashMap<String,Object>();
			
			feature.put("type", "Feature");
			
			
			String wkt = (String) m.get("geom");
			
			feature.put("geometry", JSONObject.parseObject(wkt));
			
			m.remove("geom");
			
			feature.put("properties", m);
			
			features.add(feature);
			
		}
		
		geojson.put("features", features);
		
		if (is_budian){
			 transform.trans(geojson, level);
		}

		return geojson;
	}
	
	
	@RequestMapping(value = "/json")
	public Object json(String param, int id) {

		String querySql = "select sql from data_service where id=" + id;

		String sql = jdbc.queryForList(querySql).get(0).get("sql").toString();
		
		JSONArray arrayParam = JSONArray.parseArray(param);

		if (param != null && !"[]".equals(param)) {
			

			int idx = 0;

			while (sql.indexOf("?") > 0) {

				Object valObj = arrayParam.get(idx++);

				if (valObj instanceof String) {

					//sql = sql.replace("?", "'" + valObj.toString() + "'");
					
					sql = sql.substring(0, sql.indexOf("?")) +"'" + valObj.toString() + "'" + sql.substring(sql.indexOf("?")+1); 

					
				} else {
//					sql = sql.replace("?", valObj.toString());
					
					sql = sql.substring(0, sql.indexOf("?")) + valObj.toString()  + sql.substring(sql.indexOf("?")+1); 
				}
			}

		}
		
		if (sql.indexOf(" gps ")>0){
			if (sql.indexOf("time<=?")>0 || sql.indexOf("time>=?")>0 || sql.indexOf("time between ?")>0){
				
				String datePrefix = null;
				
				for(int i=0;i<arrayParam.size();i++){
					String v = arrayParam.getString(i);
					
					if (Customer.judgeDate(v)){
						datePrefix = v.substring(0, 8);
						break;
					}
				}
				
				sql = sql.replace(" gps ", " gps_"+datePrefix+" ");
				
			}else{
				sql = Customer.convertSql(sql);
			}
		}
		
		

		List<Map<String, Object>> results = jdbc.queryForList(sql);

		Map<String,Object> map = new HashMap<String,Object>();
		
		map.put("data", results);
		
		return map;
	}

	 

	@RequestMapping(value = "/add_service")
	public Object addService(String data) {
		
		JSONObject json = JSONObject.parseObject(data);
		
		String sql = json.getString("sql");
		String trans = json.getString("trans");
		String type = json.getString("type");
		
		String describe = json.getString("describe");

		String insertSql = "insert into data_service(sql,trans,service_type,describe) values ('"
				+ sql + "','" + trans + "','" + type + "','" + describe+"')";

		jdbc.execute(insertSql);

		Map<String, Object> map = new HashMap<String, Object>();

		map.put("error_code", -1);

		map.put("error_code", "no_error");

		return map;
	}

	@RequestMapping(value = "/list_service")
	public Object serviceList() {
		
		String sql = "select id,sql,service_type as type,describe from data_service";
		
		List<Map<String,Object>> list = jdbc.queryForList(sql);
		
		Map<String,Object> result = new HashMap<String,Object>();
		
		List<Map<String,Object>> data = new ArrayList<Map<String,Object>>();
		
		result.put("data", data);
		
		for(Map<String,Object> m : list){
			String type = m.get("type").toString();
			
			if ("VectorTile".equals(type)){
				m.put("url", servicePbfPrefix+"?id="+m.get("id").toString()+"&param=[]");
			}else if ("Geojson".equals(type)){
				m.put("url", serviceGeojsonPrefix+"?id="+m.get("id").toString()+"&param=[]");
			}else{
				m.put("url", serviceJsonPrefix+"?id="+m.get("id").toString()+"&param=[]");
			}
			
			data.add(m);
		}
		
		
		return result;
	}

}
